package com.futvan.z.system.zreport;

import com.futvan.z.framework.common.bean.Code;
import com.futvan.z.framework.common.bean.Result;
import com.futvan.z.framework.core.SuperService;
import com.futvan.z.framework.core.z;
import com.futvan.z.framework.util.BeanUtil;
import com.futvan.z.framework.util.StringUtil;
import com.futvan.z.system.zdb.z_db_table_column;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletRequest;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
public class ZreportService extends SuperService{
	/**
	 * 插入数据
	 * @param bean
	 * @return
	 * @throws Exception
	 */
	public Result insertData(HashMap<String,String> bean,HttpServletRequest request) throws Exception{
		bean.put("zid", z.newZid("z_report"));
		//生成字段
		CreateReportColumn(bean);
		//生成默认按钮
		CreateReportListButton_select_button(bean.get("zid"));
		//创建菜单
		CreateReportMenu(bean);
		return insert(bean,request);
	}

	/**
	 * 修改数据 
	 * @param bean
	 * @return
	 * @throws Exception
	 */
	public Result updateData(HashMap<String,String> bean,HttpServletRequest request) throws Exception{
		//生成字段
		CreateReportColumn(bean);
		//生成默认按钮
		CreateReportButton(bean);
		//创建菜单
		CreateReportMenu(bean);
		return update(bean,request);
	}

	private void CreateReportMenu(HashMap<String, String> bean) {
		String reportid = String.valueOf(bean.get("zid"));
		int MenuCount = selectInt("SELECT COUNT(*) FROM z_menu WHERE reportid = '"+reportid+"'");
		if(MenuCount==0) {
			StringBuffer menusql = new StringBuffer();
			menusql.append("INSERT INTO z_menu(zid,parentid,name,url,menu_type,reportid,menu_icon) VALUES");
			String zid = z.newZid("z_menu");
			menusql.append(" ('"+zid+"','00000000000000000000000000000000','"+bean.get("title")+"','rlist?zid="+reportid+"','reportmenu','"+reportid+"','fa fa-bar-chart-o'); ");
			insert(menusql.toString());
			
			
			//添加按钮权限数据
			List<HashMap<String,String>> buttonList = selectList("SELECT * FROM z_report_button WHERE pid = '"+reportid+"'");
			for (HashMap<String,String> rbutton : buttonList) {
				//添加按钮权限
				String buttonSQL = "INSERT INTO z_menu_report_button(zid,pid,buttonId) VALUES ('"+z.newZid("z_menu_report_button")+"','"+zid+"','"+rbutton.get("zid")+"')";
				insert(buttonSQL);
			}
			
			
		}

	}

	/**
	 * 生成字段
	 * @param bean
	 * @throws Exception 
	 */
	private void CreateReportColumn(HashMap<String,String> bean) throws Exception {
		//解析SQL获取字段
		String sql = bean.get("sqlinfo");
		if(z.isNotNull(sql)) {

			//获取已创建的字段
			Map<String,String> old_column_map = getOldReportColumn(bean.get("zid"));

			//根据SQL解析创建字段
			Result r = AnalysisSQL(bean,StringUtil.parseExpression(sql, bean));
			HashMap sqlinfomap = (HashMap) r.getData();

			//如果获取到表名
			String tableId = (String) sqlinfomap.get("tableId");
			if(z.isNull(bean.get("tableid")) &&  z.isNotNull(tableId)){
				bean.put("tableid",tableId);
			}

			List<String[]> columnList = (List<String[]>) sqlinfomap.get("columnList");
			Map<String,String> analysisSqlColumnMap = new HashMap<String, String>();
			for (String[] columnIdArray : columnList) {
				//判读是否已经创建了字段
				if(z.isNull(old_column_map.get(columnIdArray[1]))) {

					z_db_table_column dbtc = z.dbTableColumnMap.get(bean.get("dbid")+"_"+columnIdArray[3]+"_"+columnIdArray[1]);


					//创建字段
					z_report_column col = new z_report_column();
					col.setColumn_id(columnIdArray[1]);//字段名
					col.setIs_pk(columnIdArray[2]);//是否主键
					col.setColumn_name(columnIdArray[1]);//字段描述
					if(z.isNotNull(dbtc)){
						//0 = 文本 | 1 = 多行文本 | 2 = 数字 | 3 = 文件 | 4 = 图片 | 5 = 多选 | 6 = 单选 | 7 = 下拉框 | 8 = Z5 | 9 = 日期 | 10 = 日期时间 | 11 = HTML输入框 | 12 = 源码输入框 |
						if("CHAR".equals(dbtc.getColumn_type())) {
							col.setColumn_type("0");
						}else if("INT".equals(dbtc.getColumn_type())) {
							col.setColumn_type("2");
						}else if("DATETIME".equals(dbtc.getColumn_type())) {
							col.setColumn_type("10");
						}else if("BIGINT".equals(dbtc.getColumn_type())) {
							col.setColumn_type("2");
						}else if("VARCHAR".equals(dbtc.getColumn_type())) {
							col.setColumn_type("0");
						}else{
							col.setColumn_type("0");
						}
					}else{
						col.setColumn_type("0");
					}
					col.setCompare("0");
					col.setColumn_size("3");//字段规格
					col.setColunm_length_list("150");//列表显示长度
					col.setPid(bean.get("zid"));//报表主表ID

					HashMap<String,String> ColumnBean = BeanUtil.BeanToMap(col);
					ColumnBean.put("tableId", "z_report_column");
					Result result = insert(ColumnBean,null);
					if(!Code.SUCCESS.equals(result.getCode())) {
						throw new Exception("报表管理，创建字段出错");
					}
				}

				analysisSqlColumnMap.put(columnIdArray[1], columnIdArray[1]);
			}

			//删除多余字段-遍历old_column_map
			for (String key : old_column_map.keySet()){
				if(!z.isNotNull(analysisSqlColumnMap.get(key))) {
					//删除
					delete("delete from z_report_column where pid = '"+bean.get("zid")+"' and column_id = '"+key+"'");
				}
			} 

		}else {
			throw new Exception("报表管理生成字段出错，SQL不能为空");
		}
	}

	/**
	 * 获取已生成的字段
	 * @param pid
	 * @return
	 */
	private Map<String, String> getOldReportColumn(String pid) {
		Map<String,String> columnMap = new HashMap<String,String>();
		List<HashMap<String,String>> old_column_list = selectList("SELECT column_id FROM z_report_column WHERE pid = '"+pid+"'");
		for (HashMap<String,String> columnId : old_column_list) {
			columnMap.put(columnId.get("column_id"), columnId.get("column_id"));
		}
		return columnMap;
	}

	/**
	 * 创建默认按钮
	 * @param bean
	 */
	public void CreateReportButton(HashMap<String, String> bean) {
		//添加是否添加查询按钮
		int button_count = selectInt("SELECT COUNT(*) FROM z_report_button WHERE pid = '"+bean.get("zid")+"' and button_id = 'select_button'");
		if(button_count==0) {
			String zid = z.newZid("z_form_table_button");
			String pid = String.valueOf(bean.get("zid"));
			String button_id = "select_button";
			StringBuffer button_sql = new StringBuffer();
			button_sql.append("INSERT INTO z_report_button ");
			button_sql.append(" (zid, ");
			button_sql.append(" pid, ");
			button_sql.append(" js_onclick, ");
			button_sql.append(" button_id, ");
			button_sql.append(" is_hidden, ");
			button_sql.append(" button_icon, ");
			button_sql.append(" button_name) ");
			button_sql.append(" VALUES ( ");
			button_sql.append(" '"+zid+"', ");
			button_sql.append(" '"+pid+"', ");
			button_sql.append(" 'openSelectWindows();', ");
			button_sql.append(" '"+button_id+"', ");
			button_sql.append(" '0', ");
			button_sql.append(" 'fa fa-search', ");
			button_sql.append(" '查询') ");
			insert(button_sql.toString());
		}
	}



	/**
	 * 创建报表编辑数据默认按钮
	 * @param zid
	 */
	public void CreateReportEditButton(String zid) {
		CreateReportListButton_select_button(zid);
		CreateReportListButton_add_button(zid);
		CreateReportListButton_edit_button(zid);
		CreateReportListButton_remove_button(zid);
		CreateReportListButton_look_button(zid);
		CreateReportEditButton_save_button(zid);
		CreateReportEditButton_save_and_add_button(zid);
		CreateReportEditButton_save_and_return_button(zid);
		CreateReportEditButton_return_parent_page_button(zid);
		CreateReportLookButton_return_parent_page_button(zid);
	}

	private void CreateReportLookButton_return_parent_page_button(String zid) {
		String button_id = "r_look_return_button";
		String button_name = "返回";
		String button_icon = "fa fa-hand-o-left";
		String js_onclick = "r_return();";
		String page_type = "look";//list = 列表页面 | list_card = 卡片列表 | list_look = 查看列表 | edit = 编辑页面 | look = 查看页面 |
		String function_type = "0";//0 = 跳转新页面 | 1 = 返回Json结果 | 2 = 前端JS方法 |
		int button_count = selectInt("SELECT COUNT(*) FROM z_report_button WHERE pid = '"+zid+"' and button_id = '"+button_id+"'");
		if(button_count==0) {
			StringBuffer button_sql = new StringBuffer();
			button_sql.append("INSERT INTO z_report_button ");
			button_sql.append(" (zid, ");
			button_sql.append(" pid, ");
			button_sql.append(" page_type, ");
			button_sql.append(" function_type, ");
			button_sql.append(" js_onclick, ");
			button_sql.append(" button_id, ");
			button_sql.append(" is_hidden, ");
			button_sql.append(" button_icon, ");
			button_sql.append(" button_name) ");
			button_sql.append(" VALUES ( ");
			button_sql.append(" '"+z.newZid("z_report_button")+"', ");
			button_sql.append(" '"+zid+"', ");
			button_sql.append(" '"+page_type+"', ");
			button_sql.append(" '"+function_type+"', ");
			button_sql.append(" '"+js_onclick+"', ");
			button_sql.append(" '"+button_id+"', ");
			button_sql.append(" '0', ");
			button_sql.append(" '"+button_icon+"', ");
			button_sql.append(" '"+button_name+"') ");
			insert(button_sql.toString());
		}
	}

	private void CreateReportEditButton_return_parent_page_button(String zid) {
		String button_id = "r_edit_return_button";
		String button_name = "返回";
		String button_icon = "fa fa-hand-o-left";
		String js_onclick = "r_return();";
		String page_type = "edit";//list = 列表页面 | list_card = 卡片列表 | list_look = 查看列表 | edit = 编辑页面 | look = 查看页面 |
		String function_type = "0";//0 = 跳转新页面 | 1 = 返回Json结果 | 2 = 前端JS方法 |
		int button_count = selectInt("SELECT COUNT(*) FROM z_report_button WHERE pid = '"+zid+"' and button_id = '"+button_id+"'");
		if(button_count==0) {
			StringBuffer button_sql = new StringBuffer();
			button_sql.append("INSERT INTO z_report_button ");
			button_sql.append(" (zid, ");
			button_sql.append(" pid, ");
			button_sql.append(" page_type, ");
			button_sql.append(" function_type, ");
			button_sql.append(" js_onclick, ");
			button_sql.append(" button_id, ");
			button_sql.append(" is_hidden, ");
			button_sql.append(" button_icon, ");
			button_sql.append(" button_name) ");
			button_sql.append(" VALUES ( ");
			button_sql.append(" '"+z.newZid("z_report_button")+"', ");
			button_sql.append(" '"+zid+"', ");
			button_sql.append(" '"+page_type+"', ");
			button_sql.append(" '"+function_type+"', ");
			button_sql.append(" '"+js_onclick+"', ");
			button_sql.append(" '"+button_id+"', ");
			button_sql.append(" '0', ");
			button_sql.append(" '"+button_icon+"', ");
			button_sql.append(" '"+button_name+"') ");
			insert(button_sql.toString());
		}
	}

	private void CreateReportEditButton_save_and_return_button(String zid) {
		String button_id = "r_save_and_return_button";
		String button_name = "保存并返回";
		String button_icon = "fa fa-reply-all";
		String js_onclick = "r_save_and_return();";
		String page_type = "edit";//list = 列表页面 | list_card = 卡片列表 | list_look = 查看列表 | edit = 编辑页面 | look = 查看页面 |
		String function_type = "1";//0 = 跳转新页面 | 1 = 返回Json结果 | 2 = 前端JS方法 |
		int button_count = selectInt("SELECT COUNT(*) FROM z_report_button WHERE pid = '"+zid+"' and button_id = '"+button_id+"'");
		if(button_count==0) {
			StringBuffer button_sql = new StringBuffer();
			button_sql.append("INSERT INTO z_report_button ");
			button_sql.append(" (zid, ");
			button_sql.append(" pid, ");
			button_sql.append(" page_type, ");
			button_sql.append(" function_type, ");
			button_sql.append(" js_onclick, ");
			button_sql.append(" button_id, ");
			button_sql.append(" is_hidden, ");
			button_sql.append(" button_icon, ");
			button_sql.append(" button_name) ");
			button_sql.append(" VALUES ( ");
			button_sql.append(" '"+z.newZid("z_report_button")+"', ");
			button_sql.append(" '"+zid+"', ");
			button_sql.append(" '"+page_type+"', ");
			button_sql.append(" '"+function_type+"', ");
			button_sql.append(" '"+js_onclick+"', ");
			button_sql.append(" '"+button_id+"', ");
			button_sql.append(" '0', ");
			button_sql.append(" '"+button_icon+"', ");
			button_sql.append(" '"+button_name+"') ");
			insert(button_sql.toString());
		}
	}

	private void CreateReportEditButton_save_and_add_button(String zid) {
		String button_id = "r_save_and_add_button";
		String button_name = "保存并新增";
		String button_icon = "fa fa-clipboard";
		String js_onclick = "r_save_and_add();";
		String page_type = "edit";//list = 列表页面 | list_card = 卡片列表 | list_look = 查看列表 | edit = 编辑页面 | look = 查看页面 |
		String function_type = "1";//0 = 跳转新页面 | 1 = 返回Json结果 | 2 = 前端JS方法 |
		int button_count = selectInt("SELECT COUNT(*) FROM z_report_button WHERE pid = '"+zid+"' and button_id = '"+button_id+"'");
		if(button_count==0) {
			StringBuffer button_sql = new StringBuffer();
			button_sql.append("INSERT INTO z_report_button ");
			button_sql.append(" (zid, ");
			button_sql.append(" pid, ");
			button_sql.append(" page_type, ");
			button_sql.append(" function_type, ");
			button_sql.append(" js_onclick, ");
			button_sql.append(" button_id, ");
			button_sql.append(" is_hidden, ");
			button_sql.append(" button_icon, ");
			button_sql.append(" button_name) ");
			button_sql.append(" VALUES ( ");
			button_sql.append(" '"+z.newZid("z_report_button")+"', ");
			button_sql.append(" '"+zid+"', ");
			button_sql.append(" '"+page_type+"', ");
			button_sql.append(" '"+function_type+"', ");
			button_sql.append(" '"+js_onclick+"', ");
			button_sql.append(" '"+button_id+"', ");
			button_sql.append(" '0', ");
			button_sql.append(" '"+button_icon+"', ");
			button_sql.append(" '"+button_name+"') ");
			insert(button_sql.toString());
		}
	}

	private void CreateReportEditButton_save_button(String zid) {
		String button_id = "r_save_button";
		String button_name = "保存";
		String button_icon = "fa fa-floppy-o";
		String js_onclick = "r_save();";
		String page_type = "edit";//list = 列表页面 | list_card = 卡片列表 | list_look = 查看列表 | edit = 编辑页面 | look = 查看页面 |
		String function_type = "1";//0 = 跳转新页面 | 1 = 返回Json结果 | 2 = 前端JS方法 |
		int button_count = selectInt("SELECT COUNT(*) FROM z_report_button WHERE pid = '"+zid+"' and button_id = '"+button_id+"'");
		if(button_count==0) {
			StringBuffer button_sql = new StringBuffer();
			button_sql.append("INSERT INTO z_report_button ");
			button_sql.append(" (zid, ");
			button_sql.append(" pid, ");
			button_sql.append(" page_type, ");
			button_sql.append(" function_type, ");
			button_sql.append(" js_onclick, ");
			button_sql.append(" button_id, ");
			button_sql.append(" is_hidden, ");
			button_sql.append(" button_icon, ");
			button_sql.append(" button_name) ");
			button_sql.append(" VALUES ( ");
			button_sql.append(" '"+z.newZid("z_report_button")+"', ");
			button_sql.append(" '"+zid+"', ");
			button_sql.append(" '"+page_type+"', ");
			button_sql.append(" '"+function_type+"', ");
			button_sql.append(" '"+js_onclick+"', ");
			button_sql.append(" '"+button_id+"', ");
			button_sql.append(" '0', ");
			button_sql.append(" '"+button_icon+"', ");
			button_sql.append(" '"+button_name+"') ");
			insert(button_sql.toString());
		}
	}

	private void CreateReportListButton_look_button(String zid) {
		String button_id = "r_look_button";
		String button_name = "查看";
		String button_icon = "fa fa-eye";
		String js_onclick = "r_look(obj);";
		String page_type = "list";
		String function_type = "0";//0 = 跳转新页面 | 1 = 返回Json结果 | 2 = 前端JS方法 |
		int button_count = selectInt("SELECT COUNT(*) FROM z_report_button WHERE pid = '"+zid+"' and button_id = '"+button_id+"'");
		if(button_count==0) {
			StringBuffer button_sql = new StringBuffer();
			button_sql.append("INSERT INTO z_report_button ");
			button_sql.append(" (zid, ");
			button_sql.append(" pid, ");
			button_sql.append(" page_type, ");
			button_sql.append(" function_type, ");
			button_sql.append(" js_onclick, ");
			button_sql.append(" button_id, ");
			button_sql.append(" is_hidden, ");
			button_sql.append(" button_icon, ");
			button_sql.append(" button_name) ");
			button_sql.append(" VALUES ( ");
			button_sql.append(" '"+z.newZid("z_report_button")+"', ");
			button_sql.append(" '"+zid+"', ");
			button_sql.append(" '"+page_type+"', ");
			button_sql.append(" '"+function_type+"', ");
			button_sql.append(" '"+js_onclick+"', ");
			button_sql.append(" '"+button_id+"', ");
			button_sql.append(" '0', ");
			button_sql.append(" '"+button_icon+"', ");
			button_sql.append(" '"+button_name+"') ");
			insert(button_sql.toString());
		}
	}

	private void CreateReportListButton_remove_button(String zid) {
		String button_id = "r_delete_button";
		String button_name = "删除";
		String button_icon = "fa fa-trash";
		String js_onclick = "r_delete();";
		String page_type = "list";
		String function_type = "1";//0 = 跳转新页面 | 1 = 返回Json结果 | 2 = 前端JS方法 |
		int button_count = selectInt("SELECT COUNT(*) FROM z_report_button WHERE pid = '"+zid+"' and button_id = '"+button_id+"'");
		if(button_count==0) {
			StringBuffer button_sql = new StringBuffer();
			button_sql.append("INSERT INTO z_report_button ");
			button_sql.append(" (zid, ");
			button_sql.append(" pid, ");
			button_sql.append(" page_type, ");
			button_sql.append(" function_type, ");
			button_sql.append(" js_onclick, ");
			button_sql.append(" button_id, ");
			button_sql.append(" is_hidden, ");
			button_sql.append(" button_icon, ");
			button_sql.append(" button_name) ");
			button_sql.append(" VALUES ( ");
			button_sql.append(" '"+z.newZid("z_report_button")+"', ");
			button_sql.append(" '"+zid+"', ");
			button_sql.append(" '"+page_type+"', ");
			button_sql.append(" '"+function_type+"', ");
			button_sql.append(" '"+js_onclick+"', ");
			button_sql.append(" '"+button_id+"', ");
			button_sql.append(" '0', ");
			button_sql.append(" '"+button_icon+"', ");
			button_sql.append(" '"+button_name+"') ");
			insert(button_sql.toString());
		}
	}

	private void CreateReportListButton_edit_button(String zid) {
		String button_id = "r_edit_button";
		String button_name = "修改";
		String button_icon = "fa fa-pencil-square-o";
		String js_onclick = "r_edit(obj);";
		String page_type = "list";
		String function_type = "0";//0 = 跳转新页面 | 1 = 返回Json结果 | 2 = 前端JS方法 |
		int button_count = selectInt("SELECT COUNT(*) FROM z_report_button WHERE pid = '"+zid+"' and button_id = '"+button_id+"'");
		if(button_count==0) {
			StringBuffer button_sql = new StringBuffer();
			button_sql.append("INSERT INTO z_report_button ");
			button_sql.append(" (zid, ");
			button_sql.append(" pid, ");
			button_sql.append(" page_type, ");
			button_sql.append(" function_type, ");
			button_sql.append(" js_onclick, ");
			button_sql.append(" button_id, ");
			button_sql.append(" is_hidden, ");
			button_sql.append(" button_icon, ");
			button_sql.append(" button_name) ");
			button_sql.append(" VALUES ( ");
			button_sql.append(" '"+z.newZid("z_report_button")+"', ");
			button_sql.append(" '"+zid+"', ");
			button_sql.append(" '"+page_type+"', ");
			button_sql.append(" '"+function_type+"', ");
			button_sql.append(" '"+js_onclick+"', ");
			button_sql.append(" '"+button_id+"', ");
			button_sql.append(" '0', ");
			button_sql.append(" '"+button_icon+"', ");
			button_sql.append(" '"+button_name+"') ");
			insert(button_sql.toString());
		}
	}

	private void CreateReportListButton_add_button(String zid) {
		String button_id = "r_add_button";
		String button_name = "新增";
		String button_icon = "fa fa-plus";
		String js_onclick = "r_add();";
		String page_type = "list";
		String function_type = "0";//0 = 跳转新页面 | 1 = 返回Json结果 | 2 = 前端JS方法 |
		int button_count = selectInt("SELECT COUNT(*) FROM z_report_button WHERE pid = '"+zid+"' and button_id = '"+button_id+"'");
		if(button_count==0) {
			StringBuffer button_sql = new StringBuffer();
			button_sql.append("INSERT INTO z_report_button ");
			button_sql.append(" (zid, ");
			button_sql.append(" pid, ");
			button_sql.append(" page_type, ");
			button_sql.append(" function_type, ");
			button_sql.append(" js_onclick, ");
			button_sql.append(" button_id, ");
			button_sql.append(" is_hidden, ");
			button_sql.append(" button_icon, ");
			button_sql.append(" button_name) ");
			button_sql.append(" VALUES ( ");
			button_sql.append(" '"+z.newZid("z_report_button")+"', ");
			button_sql.append(" '"+zid+"', ");
			button_sql.append(" '"+page_type+"', ");
			button_sql.append(" '"+function_type+"', ");
			button_sql.append(" '"+js_onclick+"', ");
			button_sql.append(" '"+button_id+"', ");
			button_sql.append(" '0', ");
			button_sql.append(" '"+button_icon+"', ");
			button_sql.append(" '"+button_name+"') ");
			insert(button_sql.toString());
		}
	}

	private void CreateReportListButton_select_button(String zid) {
		String button_id = "select_button";
		String button_name = "搜索";
		String button_icon = "fa fa-search";
		String js_onclick = "openSelectWindows();";
		String page_type = "list";
		String function_type = "2";//0 = 跳转新页面 | 1 = 返回Json结果 | 2 = 前端JS方法 |
		int button_count = selectInt("SELECT COUNT(*) FROM z_report_button WHERE pid = '"+zid+"' and button_id = '"+button_id+"'");
		if(button_count==0) {
			StringBuffer button_sql = new StringBuffer();
			button_sql.append("INSERT INTO z_report_button ");
			button_sql.append(" (zid, ");
			button_sql.append(" pid, ");
			button_sql.append(" page_type, ");
			button_sql.append(" function_type, ");
			button_sql.append(" js_onclick, ");
			button_sql.append(" button_id, ");
			button_sql.append(" is_hidden, ");
			button_sql.append(" button_icon, ");
			button_sql.append(" button_name) ");
			button_sql.append(" VALUES ( ");
			button_sql.append(" '"+z.newZid("z_report_button")+"', ");
			button_sql.append(" '"+zid+"', ");
			button_sql.append(" '"+page_type+"', ");
			button_sql.append(" '"+function_type+"', ");
			button_sql.append(" '"+js_onclick+"', ");
			button_sql.append(" '"+button_id+"', ");
			button_sql.append(" '0', ");
			button_sql.append(" '"+button_icon+"', ");
			button_sql.append(" '"+button_name+"') ");
			insert(button_sql.toString());
		}
	}

}
